/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v4.2.0                     */
/* Target DBMS:           MS SQL Server 2005                              */
/* Project file:          Database.dez                                    */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2012-02-07 22:48                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "Member"                                                     */
/* ---------------------------------------------------------------------- */
CREATE DATABASE booking
USE booking

CREATE TABLE [Member] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [RoleId] TINYINT NOT NULL,
    [UserName] VARCHAR(50) NOT NULL,
    [Password] VARCHAR(50) NOT NULL,
    [CreditCard] VARCHAR(50),
    [ActiveLogin] BIT CONSTRAINT [DEF_Member_ActiveLogin] DEFAULT 1 NOT NULL,
    [Hash] VARCHAR(50) NOT NULL,
    CONSTRAINT [PK_Member] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_Member_1] UNIQUE ([UserName]),
    CONSTRAINT [TUC_Member_2] UNIQUE ([CreditCard])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "CreditCard"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE [CreditCard] (
    [Code] VARCHAR(50) NOT NULL,
    [Money] FLOAT NOT NULL,
    [Active] BIT CONSTRAINT [DEF_CreditCard_Active] DEFAULT 1,
    CONSTRAINT [PK_CreditCard] PRIMARY KEY ([Code])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Customer"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Customer] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [CustomerType] TINYINT NOT NULL,
    [MemberId] BIGINT CONSTRAINT [DEF_Customer_MemberId] DEFAULT 0,
    [FirstName] NVARCHAR(40) NOT NULL,
    [LastName] NVARCHAR(60) NOT NULL,
    [Address] NVARCHAR(100) NOT NULL,
    [PhoneNumber] VARCHAR(20) NOT NULL,
    [Email] VARCHAR(50) NOT NULL,
    [Age] INTEGER NOT NULL,
    [Sex] BIT CONSTRAINT [DEF_Customer_Sex] DEFAULT 1 NOT NULL,
    [SkyMiles] BIGINT NOT NULL,
    [Passport] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_Customer_1] UNIQUE ([Passport])
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'1: Male 0: Female', N'user', N'dbo',N'table', N'Customer', N'column', N'Sex'
GO

/* ---------------------------------------------------------------------- */
/* Add table "CustomerType"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [CustomerType] (
    [Id] TINYINT IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    [Active] BIT CONSTRAINT [DEF_CustomerType_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_CustomerType] PRIMARY KEY ([Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Ticket"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Ticket] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [MemberId] BIGINT NOT NULL,
    [CustomerId] BIGINT NOT NULL,
    [FlightId] BIGINT NOT NULL,
    [ClassId] TINYINT NOT NULL,
    [Code] VARCHAR(50) NOT NULL,
    [Status] TINYINT NOT NULL,
    [FinalPrice] MONEY NOT NULL,
    [RefundPercent] TINYINT,
    [RefundPrice] MONEY,
    [SentMail] BIT,
    CONSTRAINT [PK_Ticket] PRIMARY KEY ([Id]),
    CONSTRAINT [TCC_Ticket_1] CHECK (FinalPrice > 0)
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'Blocking, confirmed, canceled, deleted', N'user', N'dbo',N'table', N'Ticket', N'column', N'Status'
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'% of the amount that would be refunded if the user cancels the ticket', N'user', N'dbo',N'table', N'Ticket', N'column', N'RefundPercent'
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'Total of price that would be refunded if the user cancels the ticket', N'user', N'dbo',N'table', N'Ticket', N'column', N'RefundPrice'
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'Mark the ticket had been sent mail or not', N'user', N'dbo',N'table', N'Ticket', N'column', N'SentMail'
GO

/* ---------------------------------------------------------------------- */
/* Add table "Class"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Class] (
    [Id] TINYINT IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    [PercentPrice] INTEGER NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Class_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Class] PRIMARY KEY ([Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Flight"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Flight] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [PlaneId] BIGINT NOT NULL,
    [MapId] BIGINT NOT NULL,
    [FlightCode] VARCHAR(10) NOT NULL,
    [StandardPrice] MONEY NOT NULL,
    [DepartureTime] DATETIME NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Flight_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Flight] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_Flight_1] UNIQUE ([FlightCode]),
    CONSTRAINT [TCC_Flight_2] CHECK (StandardPrice > 0)
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "FlightClassSeat"                                            */
/* ---------------------------------------------------------------------- */

CREATE TABLE [FlightClassSeat] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [FlightId] BIGINT NOT NULL,
    [ClassId] TINYINT NOT NULL,
    [NumberSeats] INTEGER CONSTRAINT [DEF_FlightClassSeat_NumberSeats] DEFAULT 0 NOT NULL,
    CONSTRAINT [PK_FlightClassSeat] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_FlightClassSeat_1] UNIQUE ([FlightId], [ClassId]),
    CONSTRAINT [TCC_FlightClassSeat_2] CHECK (NumberSeats > 0)
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Map"                                                        */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Map] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [OriginalLocation] INTEGER NOT NULL,
    [DestinationLocation] INTEGER NOT NULL,
    [Duration] INTEGER NOT NULL,
    [Distance] BIGINT NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Map_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Map] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_Map_1] UNIQUE ([DestinationLocation], [OriginalLocation]),
    CONSTRAINT [TCC_Map_2] CHECK (Duration > 0),
    CONSTRAINT [TCC_Map_3] CHECK (Distance > 0)
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'Time to move from original location to destination location (calculated by minutes)', N'user', N'dbo',N'table', N'Map', N'column', N'Duration'
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'Distance between original location and destination location (calculated by meter)', N'user', N'dbo',N'table', N'Map', N'column', N'Distance'
GO

/* ---------------------------------------------------------------------- */
/* Add table "Location"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Location] (
    [Id] INTEGER IDENTITY(1,1) NOT NULL,
    [Name] NVARCHAR(100) NOT NULL,
    [CountryId] INTEGER NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Location_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Location] PRIMARY KEY ([Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Country"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Country] (
    [Id] INTEGER IDENTITY(1,1) NOT NULL,
    [Name] NVARCHAR(100) NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Country_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Country] PRIMARY KEY ([Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Plane"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Plane] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    [PlaneTypeId] INTEGER NOT NULL,
    [ProducingTime] DATETIME NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Plane_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Plane] PRIMARY KEY ([Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "PlaneClassSeat"                                             */
/* ---------------------------------------------------------------------- */

CREATE TABLE [PlaneClassSeat] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [PlaneId] BIGINT NOT NULL,
    [ClassId] TINYINT NOT NULL,
    [NumberSeats] INTEGER NOT NULL,
    CONSTRAINT [PK_PlaneClassSeat] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_PlaneClassSeat_1] UNIQUE ([PlaneId], [ClassId]),
    CONSTRAINT [TCC_PlaneClassSeat_2] CHECK (NumberSeats > 0)
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "PlaneType"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE [PlaneType] (
    [Id] INTEGER IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    [Manufacture] VARCHAR(50) NOT NULL,
    [TotalSeats] INTEGER NOT NULL,
    CONSTRAINT [PK_PlaneType] PRIMARY KEY ([Id]),
    CONSTRAINT [TCC_PlaneType_1] CHECK (TotalSeats > 0)
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Role"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Role] (
    [Id] TINYINT IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Role_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Role] PRIMARY KEY ([Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "CreditCardLog"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE [CreditCardLog] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [CreditCard] VARCHAR(50) NOT NULL,
    [Action] VARCHAR(50) NOT NULL,
    [Money] MONEY NOT NULL,
    [Created_Date] DATETIME NOT NULL,
    CONSTRAINT [PK_CreditCardLog] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_CreditCardLog_1] UNIQUE ([CreditCard]),
    CONSTRAINT [TCC_CreditCardLog_2] CHECK (Money > 0)
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', N'Withdraw, refund, init', N'user', N'dbo',N'table', N'CreditCardLog', N'column', N'Action'
GO

/* ---------------------------------------------------------------------- */
/* Add table "Router"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Router] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [OriginalLocation] INTEGER NOT NULL,
    [DestinationLocation] INTEGER NOT NULL,
    [Active] BIT CONSTRAINT [DEF_Router_Active] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Router] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_Router_1] UNIQUE ([OriginalLocation], [DestinationLocation])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "RouterDetail"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [RouterDetail] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [RouterId] BIGINT NOT NULL,
    [FlightId] BIGINT NOT NULL,
    CONSTRAINT [PK_RouterDetail] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_RouterDetail_1] UNIQUE ([RouterId], [FlightId])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "RegisterRouter"                                             */
/* ---------------------------------------------------------------------- */

CREATE TABLE [RegisterRouter] (
    [Id] BIGINT IDENTITY(1,1) NOT NULL,
    [RouterId] BIGINT NOT NULL,
    [CustomerId] BIGINT NOT NULL,
    CONSTRAINT [PK_RegisterRouter] PRIMARY KEY ([Id]),
    CONSTRAINT [TUC_RegisterRouter_1] UNIQUE ([RouterId], [CustomerId])
)
GO

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [Member] ADD CONSTRAINT [CreditCard_Member] 
    FOREIGN KEY ([CreditCard]) REFERENCES [CreditCard] ([Code])
GO

ALTER TABLE [Member] ADD CONSTRAINT [Role_Member] 
    FOREIGN KEY ([RoleId]) REFERENCES [Role] ([Id])
GO

ALTER TABLE [Customer] ADD CONSTRAINT [CustomerType_Customer] 
    FOREIGN KEY ([CustomerType]) REFERENCES [CustomerType] ([Id])
GO

ALTER TABLE [Customer] ADD CONSTRAINT [Member_Customer] 
    FOREIGN KEY ([MemberId]) REFERENCES [Member] ([Id])
GO

ALTER TABLE [Ticket] ADD CONSTRAINT [Member_Ticket] 
    FOREIGN KEY ([MemberId]) REFERENCES [Member] ([Id])
GO

ALTER TABLE [Ticket] ADD CONSTRAINT [Customer_Ticket] 
    FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([Id])
GO

ALTER TABLE [Ticket] ADD CONSTRAINT [Class_Ticket] 
    FOREIGN KEY ([ClassId]) REFERENCES [Class] ([Id])
GO

ALTER TABLE [Ticket] ADD CONSTRAINT [Flight_Ticket] 
    FOREIGN KEY ([FlightId]) REFERENCES [Flight] ([Id])
GO

ALTER TABLE [Flight] ADD CONSTRAINT [Plane_Flight] 
    FOREIGN KEY ([PlaneId]) REFERENCES [Plane] ([Id])
GO

ALTER TABLE [Flight] ADD CONSTRAINT [Map_Flight] 
    FOREIGN KEY ([MapId]) REFERENCES [Map] ([Id])
GO

ALTER TABLE [FlightClassSeat] ADD CONSTRAINT [Flight_FlightClassSeat] 
    FOREIGN KEY ([FlightId]) REFERENCES [Flight] ([Id])
GO

ALTER TABLE [FlightClassSeat] ADD CONSTRAINT [Class_FlightClassSeat] 
    FOREIGN KEY ([ClassId]) REFERENCES [Class] ([Id])
GO

ALTER TABLE [Map] ADD CONSTRAINT [Location_Map_original] 
    FOREIGN KEY ([OriginalLocation]) REFERENCES [Location] ([Id])
GO

ALTER TABLE [Map] ADD CONSTRAINT [Location_Map_destination] 
    FOREIGN KEY ([DestinationLocation]) REFERENCES [Location] ([Id])
GO

ALTER TABLE [Location] ADD CONSTRAINT [Country_Location] 
    FOREIGN KEY ([CountryId]) REFERENCES [Country] ([Id])
GO

ALTER TABLE [Plane] ADD CONSTRAINT [PlaneType_Plane] 
    FOREIGN KEY ([PlaneTypeId]) REFERENCES [PlaneType] ([Id])
GO

ALTER TABLE [PlaneClassSeat] ADD CONSTRAINT [Plane_PlaneClassSeat] 
    FOREIGN KEY ([PlaneId]) REFERENCES [Plane] ([Id])
GO

ALTER TABLE [PlaneClassSeat] ADD CONSTRAINT [Class_PlaneClassSeat] 
    FOREIGN KEY ([ClassId]) REFERENCES [Class] ([Id])
GO

ALTER TABLE [CreditCardLog] ADD CONSTRAINT [CreditCard_CreditCardLog] 
    FOREIGN KEY ([CreditCard]) REFERENCES [CreditCard] ([Code])
GO

ALTER TABLE [Router] ADD CONSTRAINT [Location_Router_original] 
    FOREIGN KEY ([OriginalLocation]) REFERENCES [Location] ([Id])
GO

ALTER TABLE [Router] ADD CONSTRAINT [Location_Router_destination] 
    FOREIGN KEY ([DestinationLocation]) REFERENCES [Location] ([Id])
GO

ALTER TABLE [RouterDetail] ADD CONSTRAINT [Flight_RouterDetail] 
    FOREIGN KEY ([FlightId]) REFERENCES [Flight] ([Id])
GO

ALTER TABLE [RouterDetail] ADD CONSTRAINT [Router_RouterDetail] 
    FOREIGN KEY ([RouterId]) REFERENCES [Router] ([Id])
GO

ALTER TABLE [RegisterRouter] ADD CONSTRAINT [Customer_RegisterRouter] 
    FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([Id])
GO

ALTER TABLE [RegisterRouter] ADD CONSTRAINT [Router_RegisterRouter] 
    FOREIGN KEY ([RouterId]) REFERENCES [Router] ([Id])
GO
